In a database management system (DBMS), data is stored in one or more data containers, each container contains records, and the data within each record is organized into one or more fields. In relational database systems, the data containers are referred to as tables, the records are referred to as rows, and the fields are referred to as columns. In object oriented databases, the data containers are referred to as object classes, the records are referred to as objects, and the fields are referred to as attributes. Other database architectures may use other terminology.
Embodiments are not limited to any particular type of data container or database architecture. However, for the purpose of explanation, the examples and the terminology used herein shall be that typically associated with relational databases. Thus, the terms “table”, “row” and “column” shall be used herein to refer respectively to the data container, record, and field.
For various reasons, it is not desirable for certain users to have access to all of the columns of a table. For example, one column of an employee table may hold the salaries for the employees. Under these circumstances, it may be desirable to limit access to the salary column to management, and allow all employees to have access to the other columns. To address this situation, the employees may be restricted from directly accessing the table. Instead, they may be allowed to indirectly access the appropriate columns in the table through a “view”.
A view is a logical table. As logical tables, views may be queried by users as if they were a table. However, views actually present data that is extracted or derived from existing tables. Thus, the problem described above may be solved by (1) creating a view that extracts data from all columns of the employee table except the salary column, and (2) allowing all employees to access the view.
A view is defined by metadata referred to as a view definition. The view definition contains mappings to one or more columns in the one or more tables containing the data. Columns and tables that are mapped to a view are referred to herein as base columns and base tables of the view, respectively.
Typically, the view definition is in the form of a database query. These queries, like any database query, must conform to the rules of a particular query language such as the ANSI Structured Query Language (SQL).
The data presented by conventional views is gathered and derived on-the-fly from the base tables in response to queries that access the views. The data gathered for the view is not persistently stored after the query accessing the view has been processed. Because the data provided by conventional views is gathered from the base tables at the time the views are accessed, the data from the views will reflect the current state of the base tables. However, the overhead associated with gathering the data from the base tables for a view every time the view is accessed may be prohibitive.
A materialized view, on the other hand, is a view for which a copy of the view data is stored separate from the one or more base tables from which the data was originally gathered and derived. The data contained in a materialized view is referred to herein as “materialized data.” A materialized view eliminates the overhead associated with gathering and deriving materialized data every time a query accesses the materialized view.
When a materialized view is initially created, it is “fresh,” meaning the content of the materialized view is in sync with one or more base tables from which the materialized view is based. In response to a database update operation to one of the base table(s), such as an insert, delete, or update, one or more rows of the base table are affected. Once the transaction in which the operation is performed is committed, the materialized view becomes “stale” because the newly inserted rows are not counted in the materialized view. Also, any deleted or updated rows are not reflected in the materialized view either. Once a materialized view becomes stale, the materialized view cannot be used to answer a query, unless the session in which the query is submitted operates in a “stale tolerance” mode. The materialized view must be updated or “refreshed” in order to bring the materialized view up to date.
One approach for refreshing a materialized view is referred to as non-atomic refresh. In non-atomic refresh, the materialized view is truncated as part of the refresh. If a query is executed against the materialized view immediately after the truncate step, then the query result will be inconsistent. Another approach for refreshing a materialized view is referred to as atomic refresh. In atomic refresh, a truncate is not performed; thus, an inconsistent result will not occur. However, the query execution engine will access stale results during the refresh. When the amount of changes to the materialized view are significant, which is typical in data warehouse applications, atomic refresh may take a considerable amount of time (e.g., hours or even days) to perform due to the fact that it must use conventional DML. Non-atomic refresh is usually much more efficient in such cases because non-atomic refresh involves table/partition truncate with a subsequent insert. Therefore, non-atomic refresh is more popular in data warehouse applications.
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.